package com.hnqcgczy.supplier.dao.impl;

import com.hnqcgczy.supplier.dao.CompanyDao;
import com.hnqcgczy.supplier.db.MyTransaction;
import com.hnqcgczy.supplier.utils.BaseDao;
import com.hnqcgczy.supplier.vo.Company;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CompanyDaoImpl extends BaseDao implements CompanyDao {

    public CompanyDaoImpl(MyTransaction transaction) {
        //构建子类对象必须构建其父类对象
        super(transaction);
    }

    @Override
    public int addCompany(Company company) throws SQLException {
        //1.准备sql语句
        String sql = "INSERT INTO `t_company`(`company_id`, `company_name`, `company_short_name`, `company_english_name`, `registration_number`, `registered_fund`, `legal_person_name`, `staff_number`,  `main_services_range`, `other_services_range`, `registered_address`, `work_address`, `work_address_email`, `services_address_range`, `quality_commitment`, `phone`, `fax_number`, `company_url`, `reaction_speed_commitment`, `company_advantage`, `company_partner`, `company_dispute`) VALUES (null, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        //2.调用增删改的公共方法
        int i = this.update(sql, company.getCompanyName(), company.getCompanyShortName(), company.getCompanyEnglishName(), company.getRegistrationNumber(),
                company.getRegisteredFund(), company.getLegalPersonName(), company.getStaffNumber(),
                company.getMainServicesRange(), company.getOtherServicesRange(), company.getRegisteredAddress(), company.getWorkAddress(),
                company.getWorkAddressEmail(), company.getServicesAddressRange(), company.getQualityCommitment(), company.getPhone(),
                company.getFaxNumber(), company.getCompanyUrl(), company.getReactionSpeedCommitment(), company.getCompanyAdvantage(),
                company.getCompanyPartner(), company.getCompanyDispute());
        //3.关闭
        super.closeResource();
        return i;
    }

    @Override
    public List<Company> queryByLike(Company company, int start, int pageSize) throws SQLException {
        List<Company> companyList = null;
        StringBuffer sb = new StringBuffer("select company_id,company_name,company_short_name,company_english_name,registration_number,registered_fund,legal_person_name,staff_number,establishment_date,main_services_range,other_services_range,registered_address,work_address,work_address_email,services_address_range,quality_commitment,phone,fax_number,company_url,reaction_speed_commitment,company_advantage,company_partner,company_dispute from t_company where 1=1");
        if (company != null) {
            if (company.getCompanyId() != null && !"".equals(company.getCompanyId()) && company.getCompanyId() != 0) {
                sb.append(" and company_id = ").append(company.getCompanyId());
            }
            if (company.getRegistrationNumber() != null && !"".equals(company.getRegistrationNumber())) {
                sb.append(" and registration_number like '%").append(company.getRegistrationNumber()).append("%'");
            }
            if (company.getCompanyName() != null && !"".equals(company)) {
                sb.append(" and company_name like '%").append(company.getCompanyName()).append("%'");
            }
        }
        sb.append(" limit ").append(start).append(",").append(pageSize);
        //1.准备sql
        String sql = sb.toString();
        System.out.println(sql);
        //2.调查询的公共方法
        ResultSet rs = super.query(sql);

        if (rs != null) {
            companyList = new ArrayList<Company>();
            while (rs.next()) {
                Company temp = new Company();
                temp.setCompanyId(rs.getInt("company_id"));
                temp.setCompanyName(rs.getString("company_name"));
                temp.setCompanyShortName(rs.getString("company_short_name"));
                temp.setCompanyEnglishName(rs.getString("company_english_name"));
                temp.setRegistrationNumber(rs.getString("registration_number"));
                temp.setRegisteredFund(rs.getDouble("registered_fund"));
                temp.setLegalPersonName(rs.getString("legal_person_name"));
                temp.setStaffNumber(rs.getInt("staff_number"));
                temp.setEstablishmentDate(rs.getDate("establishment_date"));
                temp.setMainServicesRange(rs.getString("main_services_range"));
                temp.setOtherServicesRange(rs.getString("other_services_range"));
                temp.setRegisteredAddress(rs.getString("registered_address"));
                temp.setWorkAddress(rs.getString("work_address"));
                temp.setWorkAddressEmail(rs.getString("work_address_email"));
                temp.setServicesAddressRange(rs.getString("services_address_range"));
                temp.setQualityCommitment(rs.getString("quality_commitment"));
                temp.setPhone(rs.getString("phone"));
                temp.setFaxNumber(rs.getString("fax_number"));
                temp.setCompanyUrl(rs.getString("company_url"));
                temp.setReactionSpeedCommitment(rs.getString("reaction_speed_commitment"));
                temp.setCompanyAdvantage(rs.getString("company_advantage"));
                temp.setCompanyPartner(rs.getString("company_partner"));
                temp.setCompanyDispute(rs.getString("company_dispute"));
                //装入集合
                companyList.add(temp);
                temp = null;
            }


        }
        super.closeResource();
        return companyList;
    }

    @Override
    public int queryTotals(Company company) throws SQLException {
        StringBuffer sb = new StringBuffer("select count(*) totals from t_company where 1=1");
        if (company != null) {
            if (company.getCompanyId() != null && !"".equals(company.getCompanyId()) && company.getCompanyId() != 0) {
                sb.append(" and company_id = ").append(company.getCompanyId());
            }
            if (company.getRegistrationNumber() != null && !"".equals(company.getRegistrationNumber())) {
                sb.append(" and registration_number like '%").append(company.getRegistrationNumber()).append("%'");
            }
            if (company.getCompanyName() != null && !"".equals(company)) {
                sb.append(" and company_name like '%").append(company.getCompanyName()).append("%'");
            }
        }
        String sql = sb.toString();
        ResultSet rs = super.query(sql);
        int total = 0;
        if (rs != null) {
            while (rs.next()) {
                total = rs.getInt("totals");
            }
        }
        super.closeResource();
        return total;
    }

    @Override
    public Company findOne(Integer companyId) throws SQLException {
        String sql = "select company_id,company_name,company_short_name,company_english_name,registration_number,registered_fund,legal_person_name,staff_number,establishment_date,main_services_range,other_services_range,registered_address,work_address,work_address_email,services_address_range,quality_commitment,phone,fax_number,company_url,reaction_speed_commitment,company_advantage,company_partner,company_dispute from t_company where company_id = ?";
        ResultSet rs = super.query(sql, companyId);
        if (rs != null) {

            while (rs.next()) {
                Company temp = new Company();
                temp.setCompanyId(rs.getInt("company_id"));
                temp.setCompanyName(rs.getString("company_name"));
                temp.setCompanyShortName(rs.getString("company_short_name"));
                temp.setCompanyEnglishName(rs.getString("company_english_name"));
                temp.setRegistrationNumber(rs.getString("registration_number"));
                temp.setRegisteredFund(rs.getDouble("registered_fund"));
                temp.setLegalPersonName(rs.getString("legal_person_name"));
                temp.setStaffNumber(rs.getInt("staff_number"));
                temp.setEstablishmentDate(rs.getDate("establishment_date"));
                temp.setMainServicesRange(rs.getString("main_services_range"));
                temp.setOtherServicesRange(rs.getString("other_services_range"));
                temp.setRegisteredAddress(rs.getString("registered_address"));
                temp.setWorkAddress(rs.getString("work_address"));
                temp.setWorkAddressEmail(rs.getString("work_address_email"));
                temp.setServicesAddressRange(rs.getString("services_address_range"));
                temp.setQualityCommitment(rs.getString("quality_commitment"));
                temp.setPhone(rs.getString("phone"));
                temp.setFaxNumber(rs.getString("fax_number"));
                temp.setCompanyUrl(rs.getString("company_url"));
                temp.setReactionSpeedCommitment(rs.getString("reaction_speed_commitment"));
                temp.setCompanyAdvantage(rs.getString("company_advantage"));
                temp.setCompanyPartner(rs.getString("company_partner"));
                temp.setCompanyDispute(rs.getString("company_dispute"));
                return temp;
            }
        }
        super.closeResource();
        return null;
    }

    @Override
    public int update(Company company) throws SQLException {
        String sql = "update t_company set company_name=?, company_short_name=?, company_english_name=?," +
                " registration_number=?, registered_fund=?, legal_person_name=?, staff_number=?, establishment_date=?," +
                " main_services_range=?, other_services_range=?, registered_address=?, work_address=?, work_address_email=?," +
                " services_address_range=?, quality_commitment=?, phone=?, fax_number=?, company_url=?, reaction_speed_commitment=?," +
                " company_advantage=?, company_partner=?, company_dispute=? where company_id=? ";

        int i = this.update(sql, company.getCompanyName(), company.getCompanyShortName(), company.getCompanyEnglishName(), company.getRegistrationNumber(),
                company.getRegisteredFund(), company.getLegalPersonName(), company.getStaffNumber(), company.getEstablishmentDate(),
                company.getMainServicesRange(), company.getOtherServicesRange(), company.getRegisteredAddress(), company.getWorkAddress(),
                company.getWorkAddressEmail(), company.getServicesAddressRange(), company.getQualityCommitment(), company.getPhone(),
                company.getFaxNumber(), company.getCompanyUrl(), company.getReactionSpeedCommitment(), company.getCompanyAdvantage(),
                company.getCompanyPartner(), company.getCompanyDispute(), company.getCompanyId());

        super.closeResource();
        return i;
    }

    @Override
    public int delete(Integer companyId) throws SQLException {
        String sql = "DELETE FROM t_company WHERE company_id =?";
        int i = super.update(sql, companyId);
        return i;
    }
}
